﻿Imports MySql.Data.MySqlClient
Imports System.Data.OleDb
Imports System.IO
Public Class book
    Dim con As MySqlConnection = New MySqlConnection("server=localhost;userid=root;database=Vb-librarysys")
    Dim cmd As New MySqlCommand
    Dim da As New MySqlDataAdapter
    Dim dt As New DataTable
    Dim conn As OleDbConnection

    Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim con As MySqlConnection = New MySqlConnection("server=localhost;userid=root;database=vb-librarysys")
        Try

            con.Open()
            dt = New DataTable

            With (cmd)
                .Connection = con
                .CommandText = "Select * from book"

            End With
            da.SelectCommand = cmd
            da.Fill(dt)

            DataGridView1.DataSource = dt


            Dim r As DataRow
            TextBox1.AutoCompleteCustomSource.Clear()

            For Each r In dt.Rows

                TextBox1.AutoCompleteCustomSource.Add(r.Item("borrower_id").ToString)
            Next
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        da.Dispose()

        con.Close()
    End Sub


    Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
        Try

            con.Open()
            dt = New DataTable

            With (cmd)
                .Connection = con
                .CommandText = "Select * from book where borrower_id like '" & TextBox1.Text & "%'"

            End With

            da.SelectCommand = cmd
            da.Fill(dt)

            DataGridView1.DataSource = dt

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        da.Dispose()

        con.Close()
    End Sub

    Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick

    End Sub


    Private Sub Label2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label2.Click
        borrower.Show()
    End Sub



    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim dlg As New SaveFileDialog
        dlg.DefaultExt = "*.accdb"
        dlg.Filter = "ACCESS 2007|*.accdb|All File|*"
        If dlg.ShowDialog = Windows.Forms.DialogResult.OK Then
            con.Close()
            File.Copy(Application.StartupPath & "\db.accdb", dlg.FileName, True)
            con.Open()
        End If
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim dlg As New OpenFileDialog
        dlg.DefaultExt = "*.accdb"
        dlg.Filter = "ACCESS 2007|*.accdb|All File|*"
        If dlg.ShowDialog = Windows.Forms.DialogResult.OK Then
            con.Close()
            File.Copy(dlg.FileName, Application.StartupPath & "\db.accdb", True)
            Form3_Load(Nothing, Nothing)
        End If
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Try
            Dim com As New OleDbCommand("DELETE FROM book WHERE ID = " & DataGridView1.Item(0, 0).Value, conn)
            MessageBox.Show(com.CommandText)
            com.ExecuteNonQuery()
            con.Dispose()
            Form3_Load(Nothing, Nothing)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

End Class